<?php
	class PO_Model_1 extends CI_Model {
		var $table = "po_";
		//digunakan untuk perhitungan laporan stock cabang
		var $hutang_barang_pcs = array();
		var $hutang_barang_set = array();
		var $sisa_barang_pcs = array();
		var $sisa_barang_set = array();
		//
		function get_barang(){
			$query	=	$this->db->query('select upper(barang.strnama) strnama, barang.intid_barang intid, harga.* from barang 
				left join harga on harga.intid_barang = barang.intid_barang where barang.status_barang = 1');
			return $query->result();
		}
		function get_nopo($id,$week,$ket){
			$var = "";
				$query = $this->db->query('select id from counter_ where keterangan = "'.$ket.'"');
				$check0  = $query->result();
				
				$data = array(
					   'id' => $check0[0]->id +1
					);
				$this->db->where('keterangan',$ket);
				$this->db->update('counter_', $data); 
				
				$query = $this->db->query('select id from counter_ where keterangan = "'.$ket.'"');
				$check  = $query->result();
				
				$var = $check[0]->id.'/'.$week.'/'.$id.'/'.strtoupper($ket).'/'.date('m').'/'.date('Y');
				return $var;
			}
		function insert_DataPO($var,$data){
			$table = "";
				if($var == 1){
					$table = "po_";
					}else{
						$table = "po_detail_";
						}
					$this->db->insert($table,$data);
			}
		function insert_DataRetur($var,$data){
			$table = "";
				if($var == 1){
					$table = "retur_";
					}else{
						$table = "retur_detail_";
						}
					$this->db->insert($table,$data);
			}		
		function DataPO(){
			$query = $this->db->query('select pod.*,
									  b.strnama,
									  (select strnama_cabang 
									   	from cabang c inner join po_ po on po.intid_cabang = c.intid_cabang where po.no_po = pod.no_po group by c.intid_cabang) strnama_cabang,
									  (select time 
									   	from  po_ po where po.no_po = pod.no_po group by po.no_po) timeNow,
									  b.intid_jsatuan
									  from po_detail_ pod inner join barang b on b.intid_barang = pod.intid_barang  
									  where pod.no_po = (select max(po.no_po) no_po
							 			from po_ po) order by b.strnama asc');
			return $query->result();
			}
		function check_po_week($intid_cabang,$intid_week){
			$query2	=	$this->db->query('select count(*) cek from po_ po where po.intid_cabang = "'.$intid_cabang.'" and po.intid_week = "'.$intid_week.'"');
			$cek = $query2->result();
			return $cek[0]->cek;
			}
		function selectWeek(){
			//$thn = date('Y');
			$query = $this->db->query("select intid_week from week where curdate() between dateweek_start and dateweek_end");
			return $query->result();
			}
		function OpenDB($query){
			return $this->db->query($query);
			}
		
		function pengecekanSPKB($no_spkb){
			$query = $this->db->query('select count(*) nomor from spkb where no_spkb = "'.$no_spkb.'"');
			$no = $query->result();
			return $no[0]->nomor;
			}
		///fungsi yang baru
		//line ikhlas 03042013 masih banyak yang diperbaiki
		//pa perlu di buat intquantity_skrg untuk mengurangi penjumalahan sebelumnya
		/*
		function getLap_Stok_cab_2($intid_cabang, $week){
	
			$weekbefore = $week-1;
			$query = $this->db->query('select barang.strnama,
									  		barang.intid_barang,
											barang.intid_jsatuan, 
											barang_masuk_before.jum_barang_masuk_before, 
											barang_keluar_before.jum_barang_keluar_before, 
											barang_masuk_after.jum_barang_masuk_after, 
											barang_keluar_after.jum_barang_keluar_after,
											barang_keluar_hadiah_after.jum_barang_keluar_hadiah_after,
											stok_barang.jumlah 
									  from
									  barang left join
									  (select sum(intquantity) jum_barang_keluar_before, nd.intid_barang 
										  from 
										  nota n inner join nota_detail nd on nd.intid_nota = n.intid_nota 
										  where n.intid_cabang = "'.$intid_cabang.'"
										  and n.intid_week <= "'.$weekbefore.'"
										  and n.datetgl > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang limit 0,1) group by nd.intid_barang
										  )as barang_keluar_before
									  on barang_keluar_before.intid_barang = barang.intid_barang
									  left join
									  (select sum(spd.quantity) jum_barang_masuk_before, spd.intid_barang 
									   		from 
											spkb inner join spkb_detail spd on spd.no_spkb = spkb.no_spkb
											where spkb.intid_cabang = "'.$intid_cabang.'"
											and spkb.week <= "'.$weekbefore.'"
											and spkb.tgl_terima > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang where intid_cabang = "'.$intid_cabang.'" limit 0,1) 
											and spkb.no_sj != ""
											and terkirim = 1
											group by spd.intid_barang	
									   	  )as barang_masuk_before
									   on barang_masuk_before.intid_barang = barang.intid_barang
									   left join
									  (select sum(intquantity) jum_barang_keluar_after, nd.intid_barang 
										  from 
										  nota n inner join nota_detail nd on nd.intid_nota = n.intid_nota 
										  where n.intid_cabang = "'.$intid_cabang.'"
										  and n.intid_week = "'.$week.'"
										  and n.is_dp = 0
										  and n.datetgl > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang where intid_cabang = "'.$intid_cabang.'" limit 0,1) group by nd.intid_barang
										  )as barang_keluar_after
									  on barang_keluar_after.intid_barang = barang.intid_barang
									  left join
									  (select sum(ndh.intquantity) jum_barang_keluar_hadiah_after, ndh.intid_barang 
										   from 
										   nota_hadiah nh inner join nota_detail_hadiah ndh on ndh.intid_nota = nh.intid_nota 
										   where nh.intid_cabang = "'.$intid_cabang.'"
										   and nh.intid_week = "'.$week.'"
										   and nh.datetgl > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang where intid_cabang = "'.$intid_cabang.'" limit 0,1) group by nh.intid_barang
										   )as barang_keluar_hadiah_after
									  on barang_keluar_hadiah_after.intid_barang = barang.intid_barang
									  left join
									  (select sum(spd.quantity) jum_barang_masuk_after, spd.intid_barang 
									   		from 
											spkb inner join spkb_detail spd on spd.no_spkb = spkb.no_spkb
											where spkb.intid_cabang = "'.$intid_cabang.'"
											and spkb.week = "'.$week.'"
											and spkb.tgl_terima > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang where intid_cabang = "'.$intid_cabang.'" limit 0,1) 
											and spkb.no_sj != ""
											and terkirim = 1
											group by spd.intid_barang	
									   	  )as barang_masuk_after
									   on barang_masuk_after.intid_barang = barang.intid_barang
									   left join 
									   (select *
											from 
											stok_barang
											where intid_cabang = "'.$intid_cabang.'"
											)as stok_barang
									   on stok_barang.intid_barang = barang.intid_barang
									  order by barang.strnama asc
									  ');
			return $query->result();
		}*/
		function getLap_Stok_cab_START($intid_cabang, $week){
	
			$weekbefore = $week-1;
			$query = $this->db->query('select barang.strnama,
									  		barang.intid_barang,
											barang.intid_jsatuan, 
											"0" as jum_barang_masuk_before, 
											"0" as jum_barang_keluar_before, 
											"0" as jum_barang_keluar_hadiah_before,
											"0" as jum_barang_masuk_after, 
											"0" as jum_barang_keluar_after,
											"0" as jum_barang_keluar_hadiah_after,
											stok_barang.set_fisik, 
											stok_barang.pcs_fisik 
									  from
									  barang
									   left join 
									   (select *
											from 
											stok_barang
											where intid_cabang = "'.$intid_cabang.'"
											)as stok_barang
									   on stok_barang.intid_barang = barang.intid_barang
									  order by barang.strnama asc
									  ');
			return $query->result();
		}
		function getLap_Stok_cab_2($intid_cabang, $week){
			$query0 = $this->db->query("select date(as_of_date) start from stok_barang where intid_cabang = '".$intid_cabang."' limit 0,1 ");
			$hasil0 = $query0->result();
			$weekbefore = $week-1;
			$query1 = $this->db->query("select dateweek_end from week where intid_week = ".$weekbefore."");
			$hasil1 = $query1->result();
			if($hasil0[0]->start >= $hasil1[0]->dateweek_end){
				$weekbefore = 0 ;
			} else{
				//Do Nothing
			}
			$query = $this->db->query('select barang.strnama,
									  		barang.intid_barang,
											barang.intid_jsatuan, 
											barang_masuk_before.jum_barang_masuk_before, 
											barang_keluar_before.jum_barang_keluar_before, 
											barang_masuk_after.jum_barang_masuk_after, 
											barang_keluar_after.jum_barang_keluar_after,
											barang_keluar_hadiah_after.jum_barang_keluar_hadiah_after,
											stok_barang.set_fisik, 
											stok_barang.pcs_fisik 
									  from
									  barang left join
									  (select sum(intquantity) jum_barang_keluar_before, nd.intid_barang 
										  from 
										  nota n inner join nota_detail nd on nd.intid_nota = n.intid_nota 
										  where n.intid_cabang = "'.$intid_cabang.'"
										  and n.intid_week <= "'.$weekbefore.'"
										  and n.datetgl > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang limit 0,1) group by nd.intid_barang
										  )as barang_keluar_before
									  on barang_keluar_before.intid_barang = barang.intid_barang
									  left join
									  (select sum(spd.quantity) jum_barang_masuk_before, spd.intid_barang 
									   		from 
											spkb inner join spkb_detail spd on spd.no_spkb = spkb.no_spkb
											where spkb.intid_cabang = "'.$intid_cabang.'"
											and spkb.week <= "'.$weekbefore.'"
											and spkb.tgl_terima > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang where intid_cabang = "'.$intid_cabang.'" limit 0,1) 
											and spkb.no_sj != ""
											and terkirim = 1
											group by spd.intid_barang	
									   	  )as barang_masuk_before
									   on barang_masuk_before.intid_barang = barang.intid_barang
									   left join
									  (select sum(intquantity) jum_barang_keluar_after, nd.intid_barang 
										  from 
										  nota n inner join nota_detail nd on nd.intid_nota = n.intid_nota 
										  where n.intid_cabang = "'.$intid_cabang.'"
										  and n.intid_week = "'.$week.'"
										  and n.is_dp = 0
										  and n.datetgl > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang where intid_cabang = "'.$intid_cabang.'" limit 0,1) group by nd.intid_barang
										  )as barang_keluar_after
									  on barang_keluar_after.intid_barang = barang.intid_barang
									  left join
									  (select sum(ndh.intquantity) jum_barang_keluar_hadiah_after, ndh.intid_barang 
										   from 
										   nota_hadiah nh inner join nota_detail_hadiah ndh on ndh.intid_nota = nh.intid_nota 
										   where nh.intid_cabang = "'.$intid_cabang.'"
										   and nh.intid_week = "'.$week.'"
										   and nh.datetgl > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang where intid_cabang = "'.$intid_cabang.'" limit 0,1) group by ndh.intid_barang
										   )as barang_keluar_hadiah_after
									  on barang_keluar_hadiah_after.intid_barang = barang.intid_barang
									  left join
									  (select sum(spd.quantity) jum_barang_masuk_after, spd.intid_barang 
									   		from 
											spkb inner join spkb_detail spd on spd.no_spkb = spkb.no_spkb
											where spkb.intid_cabang = "'.$intid_cabang.'"
											and spkb.week = "'.$week.'"
											and spkb.tgl_terima > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang where intid_cabang = "'.$intid_cabang.'" limit 0,1) 
											and spkb.no_sj != ""
											and terkirim = 1
											group by spd.intid_barang	
									   	  )as barang_masuk_after
									   on barang_masuk_after.intid_barang = barang.intid_barang
									   left join 
									   (select *
											from 
											stok_barang
											where intid_cabang = "'.$intid_cabang.'"
											)as stok_barang
									   on stok_barang.intid_barang = barang.intid_barang
									  order by barang.strnama asc
									  ');
			return $query->result();
		}
		function selectWeek2($intid_cabang)
		{
		$query2 = $this->db->query("select as_of_date from stok_barang where intid_cabang = '".$intid_cabang."' limit 0,1");
		$hasil = $query2->result();
			if(isset($hasil[0]->as_of_date)){
				$temp = $hasil[0]->as_of_date;
			}else{
				$temp = "";
			}
			$query = $this->db->query("select id, intid_week 
									  from week 
									  where dateweek_start > date_format('".$temp."', '%Y-%m-%d')");
			return $query->result();
		}
		//POCO/lookup2
		function get_PO_cabang($intid_cabang){
			$query = $this->db->query("select po.*,po.time waktu,
																		c.strnama_cabang 
																		from po_ po inner join cabang c on c.intid_cabang = po.intid_cabang 
																		where po.intid_cabang = ".$intid_cabang." and
																		po.is_sj = 0
																		order by time desc");
			return $query->result();
		}
		//POCO/SJ_INSERT_SEARCH
		function SPKB_SJ_SEARCH($varcabang,$halaman,$limit){
			$query = $this->db->query('select spd.no_spkb, 
										spd.time waktu,
									  cabang.strnama_cabang 
									  from spkb spd inner join cabang on cabang.intid_cabang = spd.intid_cabang 
									  where (spd.no_sj = "" or spd.no_sj is NULL) 
									  and cabang.strnama_cabang like "'.$varcabang.'"
									  order by spd.time desc
									  limit '.$halaman.','.$limit.' ');
			return $query;
			}
		function SPKB_SJ($halaman,$limit){
			$query = $this->db->query('select spd.no_spkb, spd.time waktu,
									  (select strnama_cabang from cabang where intid_cabang = spd.intid_cabang) strnama_cabang 
									  from spkb spd where (spd.no_sj = "" or spd.no_sj is NULL) order by spd.time desc limit '.$halaman.','.$limit.'');
			return $query;
			}
		function STTB_SJ($halaman,$limit){
			$query = $this->db->query('select spd.no_sttb, spd.waktu,
									  (select strnama_cabang from cabang where intid_cabang = spd.intid_cabang) strnama_cabang 
									  from sttb spd where (spd.no_sj = "" or spd.no_sj is NULL) order by spd.waktu desc limit '.$halaman.','.$limit.'');
			return $query;
			}
		function STTB_SJ_SEARCH($varcabang,$halaman,$limit){
			$query = $this->db->query('select spd.no_sttb, 
										spd.waktu,
									  cabang.strnama_cabang 
									  from sttb spd inner join cabang on cabang.intid_cabang = spd.intid_cabang 
									  where (spd.no_sj = "" or spd.no_sj is NULL) 
									  and cabang.strnama_cabang like "'.$varcabang.'"
									  order by spd.waktu desc
									  limit '.$halaman.','.$limit.' ');
			return $query;
			}
		function get_list_data_retur($offset = 0, $id_cabang)
			{
				if($offset==""){ $offset=0; }		
			
			//	$query = $this->db->query("SELECT a.*, cabang.strnama_cabang FROM retur_ a INNER JOIN cabang ON a.intid_cabang = cabang.intid_cabang where cabang.intid_cabang = $id_cabang ORDER BY a.no_srb ASC  LIMIT $offset,$limit");
				$query = $this->db->query("SELECT a.*, cabang.strnama_cabang FROM retur_ a INNER JOIN cabang ON a.intid_cabang = cabang.intid_cabang where cabang.intid_cabang = $id_cabang ORDER BY a.no_srb ASC");
				return $query;
			}
		function countDataRetur()
			{
				return $this->db->count_all('retur_');
			}
			
		///tgl06 agustus 2013 untuk membuktikan laporan model sudah berbeda dengan fungsi yang disimpan ini
		//tgl 14 agustus 2013 sudah diubah lagi oleh ikhlas, untuk memperbaiki kesalahan tanggal kemarin
		function get_CetakPenjualanBulanan_lama($month, $cabang, $jpenjualan)
	{
		$query = $this->db->query("select 
					X.intid_dealer, 
					sum(X.omsett) omsett, 
					sum(X.omsetm) omsetm, sum(X.omsettc)omsettc, 
							sum(X.omsetlg) omsetlg,
							sum(X.omsetll) omsetll,
							X.strnama_dealer, 
							X.strnama_upline,
							X.intid_dealer,
							X.strnama_cabang, X.strnama_unit,
							(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intbulan = $month order by intid_week ASC limit 0,1) AS datestart,
							(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intbulan = $month order by intid_week DESC limit 0,1) AS dateend, 
							X.intid_week, 
							sum(X.intkomisi10) intkomisi10, 
							sum(X.intkomisi20) intkomisi20, 
							sum(X.intpv) intpv, 
							sum(X.inttotal_bayar) inttotal_bayar,
							sum(X.inttotal_omset) inttotal_omset, 
							X.strnama_jpenjualan, 
							X.intid_jpenjualan,
							'".$month."' AS intbulan,
							kodebwtmisahinomsettulipdenganmetal.tradein_t,
							kodebwtmisahinomsettulipdenganmetal.tradein_m
							from 
					(select  
						(select sum(if (nota.inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(nota.inttotal_omset =0, 0, nota.intvoucher))
							from nota
							where nota.intid_week in (select intid_week from week where intbulan = $month)
							and nota.intid_cabang = $cabang
							and nota.intid_jpenjualan = $jpenjualan
							and nota.is_dp = 0
							and nota.is_arisan = 0
							and nota.intid_nota = a.intid_nota) 
						from nota, nota_detail, barang 
						where nota.intid_nota = nota_detail.intid_nota
						and nota_detail.intid_barang = barang.intid_barang
						and nota.intid_week  in (select intid_week from week where intbulan = $month)
						and nota.intid_cabang = $cabang
						and nota.intid_jpenjualan =  $jpenjualan
						and nota_detail.is_free = 0
						and barang.intid_jbarang=1
						and nota.is_arisan = 0
						and nota.is_dp = 0
						and nota.intid_nota = a.intid_nota) as omsett,
						(select sum(if (nota.inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(nota.inttotal_omset =0, 0, nota.intvoucher))
							from nota
							where nota.intid_week in (select intid_week from week where intbulan = $month)
							and nota.intid_cabang = $cabang
							and nota.intid_jpenjualan = $jpenjualan
							and nota.is_dp = 0
							and nota.is_arisan = 0
							and nota.intid_nota = a.intid_nota) 
						from nota, nota_detail, barang 
						where nota.intid_nota = nota_detail.intid_nota
						and nota_detail.intid_barang = barang.intid_barang
						and nota.intid_week  in (select intid_week from week where intbulan = $month)
						and nota.intid_cabang = $cabang
						and nota.intid_jpenjualan =  $jpenjualan
						and nota_detail.is_free = 0
						and barang.intid_jbarang=2
						and nota.is_arisan = 0
						and nota.is_dp = 0
						and nota.intid_nota = a.intid_nota) as omsetm,
						(select sum(if (nota.inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(nota.inttotal_omset =0, 0, nota.intvoucher))
								from nota
								where nota.intid_week in (select intid_week from week where intbulan = $month)
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan = $jpenjualan
								and nota.is_arisan = 0
								and nota.intid_nota = a.intid_nota) 
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week  in (select intid_week from week where intbulan = $month)
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=3
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota
							) as omsettc,
							(select sum(nota_detail.intquantity*nota_detail.intharga)
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week  in (select intid_week from week where intbulan = $month)
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=5
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota
							) AS omsetlg, 
							(select sum(nota_detail.intquantity*nota_detail.intharga)
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week  in (select intid_week from week where intbulan = $month)
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=6
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota
							) AS omsetll,
							m.strnama_dealer, 
							m.strnama_upline,
							m.intid_dealer,
							cb.strnama_cabang, 
							u.strnama_unit,
							' ' AS dateend, ' ' AS datestart, 
							' ' AS intid_week, 
							(select strnama_jpenjualan from jenis_penjualan where intid_jpenjualan = $jpenjualan) as strnama_jpenjualan, 
							a.intid_jpenjualan,
							a.intpv,
							a.inttotal_omset,
							a.inttotal_bayar,
							a.intkomisi10,
							a.intkomisi20
						from nota a inner join nota_detail b on 
									a.intid_nota = b.intid_nota 
								inner join barang c on 
									b.intid_barang = c.intid_barang
								inner join member m on 
									m.intid_dealer = a.intid_dealer 
								inner join unit u on 
									u.intid_unit = a.intid_unit
								inner join cabang cb on 
									cb.intid_cabang = a.intid_cabang
						where  a.intid_week  in (select intid_week from week where intbulan = $month)
						and a.intid_cabang = $cabang
						and a.intid_jpenjualan = $jpenjualan
						and b.is_free = 0
						and a.is_arisan = 0
						and a.is_dp = 0 group by a.intid_nota) AS X 
			LEFT JOIN
				(SELECT jbarangt.intid_dealer, jbarangt.hasil AS tradein_t, jbarangm.hasil AS tradein_m FROM 
					(SELECT * FROM 
						(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
						FROM `nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
						INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
						WHERE intid_week  in (select intid_week from week where intbulan = $month) AND intid_jpenjualan = $jpenjualan and 
										is_arisan = 0
										and is_dp = 0
										AND intid_cabang = $cabang
							GROUP BY intid_dealer, intid_jbarang,intid_cabang) AS asd 
					WHERE intid_jbarang = 1) AS jbarangt 
		INNER JOIN 
				(SELECT * FROM 
					(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
					FROM `nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
					INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
					WHERE intid_week  in (select intid_week from week where intbulan = $month) AND intid_jpenjualan = $jpenjualan and is_arisan = 0
									and is_dp = 0
										AND intid_cabang = $cabang
					GROUP BY intid_dealer, intid_jbarang) AS asd 
				WHERE intid_jbarang = 2) AS jbarangm 
ON jbarangt.intid_dealer = jbarangm.intid_dealer
where jbarangt.intid_cabang = $cabang
	) AS kodebwtmisahinomsettulipdenganmetal
	on X.intid_dealer = kodebwtmisahinomsettulipdenganmetal.intid_dealer
						group by X.intid_dealer");
        return $query->result();
	}
		function get_CetakPenjualanBulanan2($month, $cabang, $jpenjualan){
			$select = "select X.intid_dealer, 
							X.omsett, 
							X.omsetm, 
							X.omsettc, 
							X.omsetlg,
							X.omsetll,
							X.strnama_dealer, 
							X.strnama_upline,
							X.intid_dealer,
							X.strnama_cabang, 
							X.strnama_unit,
							(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intbulan = $month order by intid_week ASC limit 0,1) AS datestart,
							(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intbulan = $month order by intid_week DESC limit 0,1) AS dateend, 
							X.intid_week, 
							X.intkomisi10, 
							X.intkomisi20, 
							X.intpv, 
							X.inttotal_bayar,
							X.inttotal_omset, 
							X.strnama_jpenjualan, 
							X.intid_jpenjualan,
							'".$month."' AS intbulan,
							X.tradein_t,
							X.tradein_m
							from (
							select 
								(select sum(if(nota.inttotal_omset = 0,0,(nota_detail.intquantity*nota_detail.intharga))) 
									from nota 
										inner join nota_detail on nota_detail.intid_nota = nota.intid_nota
										inner join barang on barang.intid_barang = nota_detail.intid_barang
											where 
												nota.intid_nota = n.intid_nota and
												barang.intid_jbarang = 1
										group by nota.intid_nota
									) omsett,
									(select sum(if(nota.inttotal_omset = 0,0,(nota_detail.intquantity*nota_detail.intharga))) 
									from nota 
										inner join nota_detail on nota_detail.intid_nota = nota.intid_nota
										inner join barang on barang.intid_barang = nota_detail.intid_barang
											where 
												nota.intid_nota = n.intid_nota and
												barang.intid_jbarang = 2
										group by nota.intid_nota
									) omsetm,
									(select sum(if(nota.inttotal_omset = 0,0,(nota_detail.intquantity*nota_detail.intharga))) 
									from nota 
										inner join nota_detail on nota_detail.intid_nota = nota.intid_nota
										inner join barang on barang.intid_barang = nota_detail.intid_barang
											where 
												nota.intid_nota = n.intid_nota and
												barang.intid_jbarang = 3
										group by nota.intid_nota
									) omsettc,
									(select sum(if(nota.inttotal_omset = 0,0,(nota_detail.intquantity*nota_detail.intharga))) 
									from nota 
										inner join nota_detail on nota_detail.intid_nota = nota.intid_nota
										inner join barang on barang.intid_barang = nota_detail.intid_barang
											where 
												nota.intid_nota = n.intid_nota and
												barang.intid_jbarang = 5
										group by nota.intid_nota
									) omsetlg,
									(select sum(if(nota.inttotal_omset = 0,0,(nota_detail.intquantity*nota_detail.intharga))) 
									from nota 
										inner join nota_detail on nota_detail.intid_nota = nota.intid_nota
										inner join barang on barang.intid_barang = nota_detail.intid_barang
											where 
												nota.intid_nota = n.intid_nota and
												barang.intid_jbarang = 6
										group by nota.intid_nota
									) omsetll,
									(SELECT SUM(nota_detail.intharga * nota_detail.intquantity * (1 - (nota.inttrade_in * 0.01))) AS hasil
										FROM 
											`nota` 
											INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
											INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
										WHERE 
											barang.intid_jbarang = 1 and
											nota.intid_nota = n.intid_nota
										GROUP BY nota.intid_nota
									) tradein_t,
									(SELECT SUM(nota_detail.intharga * nota_detail.intquantity * (1 - (nota.inttrade_in * 0.01))) AS hasil
										FROM 
											`nota` 
											INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
											INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
										WHERE 
											barang.intid_jbarang = 2 and
											nota.intid_nota = n.intid_nota
										GROUP BY nota.intid_nota
									) tradein_m,
								m.strnama_dealer, 
								m.strnama_upline,
								m.intid_dealer,
								c.strnama_cabang, 
								u.strnama_unit,
								n.intkomisi10, 
								n.intkomisi20, 
								n.intpv, 
								n.inttotal_bayar,
								n.inttotal_omset, 
								j.strnama_jpenjualan, 
								n.intid_jpenjualan,
								'' AS intid_week
								from nota n
									inner join nota_detail nd on nd.intid_nota = n.intid_nota
									inner join member m on m.intid_dealer = n.intid_dealer
									inner join unit u on u.intid_unit = n.intid_unit
									inner join cabang c on c.intid_cabang = n.intid_nota
									inner join jenis_penjualan j on j.intid_jpenjualan = n.intid_jpenjualan
								where
									n.is_dp = 0 and 
									n.is_arisan = 0 and
									n.intid_week in (select intid_week from week where intbulan = $month) and 
									n.intid_jpenjualan = $jpenjualan and
									n.intid_cabang = $cabang
								group by n.intid_nota
							) AS X
							";
			$query =  $this->db->query($select);
			return $query->result();
		}
	//tanggal diubah 14 agustus 2013 10:11am oleh ikhlas firlana
	/*
	function get_CetakPenjualanMingguan($week, $cabang, $jpenjualan)
	{
		$query = $this->db->query("select X.intid_dealer, sum(X.omsett) omsett, sum(X.omsetm) omsetm, sum(X.omsettc) omsettc, 
							sum(X.omsetlg) omsetlg,
							sum(X.omsetll) omsetll,
							X.strnama_dealer, 
							X.strnama_upline,
							X.intid_dealer,
							X.strnama_cabang, X.strnama_unit,
							(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intid_week = $week) AS datestart,
							(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week = $week) AS dateend, 
							X.intid_week, 
							sum(X.intkomisi10) intkomisi10, 
							sum(X.intkomisi20) intkomisi20, 
							sum(X.intpv) intpv, 
							sum(X.inttotal_bayar) inttotal_bayar,
							sum(X.inttotal_omset) inttotal_omset, 
							X.strnama_jpenjualan, 
							X.intid_jpenjualan,
							kodebwtmisahinomsettulipdenganmetal.tradein_t,
							kodebwtmisahinomsettulipdenganmetal.tradein_m
							from 
					(select  
						(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
							from nota
							where intid_week = $week
							and intid_cabang = $cabang
							and intid_jpenjualan = $jpenjualan
							and is_arisan = 0
							and intid_nota = a.intid_nota) 
							from nota, nota_detail, barang 
							where nota.intid_nota = nota_detail.intid_nota
							and nota_detail.intid_barang = barang.intid_barang
							and nota.intid_week = $week
							and nota.intid_cabang = $cabang
							and nota.intid_jpenjualan =  $jpenjualan
							and nota_detail.is_free = 0
							and barang.intid_jbarang=1
							and nota.is_arisan = 0
							and nota.is_dp = 0
							and nota.intid_nota = a.intid_nota) as omsett,
						(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
						from nota
						where intid_week = $week
						and intid_cabang = $cabang
						and intid_jpenjualan = $jpenjualan
						and is_arisan = 0
						and intid_nota = a.intid_nota) 
						from nota, nota_detail, barang 
						where nota.intid_nota = nota_detail.intid_nota
						and nota_detail.intid_barang = barang.intid_barang
						and nota.intid_week = $week
						and nota.intid_cabang = $cabang
						and nota.intid_jpenjualan =  $jpenjualan
						and nota_detail.is_free = 0
						and barang.intid_jbarang=2
						and nota.is_arisan = 0
						and nota.is_dp = 0
						and nota.intid_nota = a.intid_nota) as omsetm,
						(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
								from nota
								where intid_week = $week
								and intid_cabang = $cabang
								and intid_jpenjualan = $jpenjualan
								and is_arisan = 0
								and intid_nota = a.intid_nota) 
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week = $week
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=3
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota
							) as omsettc,
							(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
								from nota
								where intid_week = $week
								and intid_cabang = $cabang
								and intid_jpenjualan = $jpenjualan
								and is_arisan = 0
								and intid_nota = a.intid_nota) 
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week = $week
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=5
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota
							) AS omsetlg, 
							(select sum(if (inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
								from nota
								where intid_week = $week
								and intid_cabang = $cabang
								and intid_jpenjualan = $jpenjualan
								and is_arisan = 0
								and intid_nota = a.intid_nota) 
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week = $week
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=6
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota
							) AS omsetll,
							m.strnama_dealer, 
							m.strnama_upline,
							m.intid_dealer,
							cb.strnama_cabang, 
							u.strnama_unit,
							'".$week."' AS dateend, '".$week."' AS datestart, 
							a.intid_week, 
							(select strnama_jpenjualan from jenis_penjualan where intid_jpenjualan = $jpenjualan) as strnama_jpenjualan, 
							a.intid_jpenjualan,
							a.intpv,
							a.inttotal_omset,
							a.inttotal_bayar,
							a.intkomisi10,
							a.intkomisi20
						from nota a inner join nota_detail b on 
									a.intid_nota = b.intid_nota 
								inner join barang c on 
									b.intid_barang = c.intid_barang
								inner join member m on 
									m.intid_dealer = a.intid_dealer 
								inner join unit u on 
									u.intid_unit = a.intid_unit
								inner join cabang cb on 
									cb.intid_cabang = a.intid_cabang
						where  a.intid_week = $week
						and a.intid_cabang = $cabang
						and a.intid_jpenjualan = $jpenjualan
						and b.is_free = 0
						and a.is_dp = 0 group by a.intid_nota) AS X 
			LEFT JOIN
				(SELECT jbarangt.intid_dealer, jbarangt.hasil AS tradein_t, jbarangm.hasil AS tradein_m FROM 
	(SELECT * FROM 
		(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
		FROM `nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
		INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
		WHERE intid_week = $week AND intid_jpenjualan = $jpenjualan
			GROUP BY intid_dealer, intid_jbarang,intid_cabang) AS asd 
	WHERE intid_jbarang = 1) AS jbarangt 
INNER JOIN 
	(SELECT * FROM 
		(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
		FROM `nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
		INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
		WHERE intid_week = $week AND intid_jpenjualan = $jpenjualan 
		GROUP BY intid_dealer, intid_jbarang) AS asd 
	WHERE intid_jbarang = 2) AS jbarangm 
ON jbarangt.intid_dealer = jbarangm.intid_dealer
where jbarangt.intid_cabang = $cabang
	) AS kodebwtmisahinomsettulipdenganmetal
	on X.intid_dealer = kodebwtmisahinomsettulipdenganmetal.intid_dealer
						group by X.intid_dealer");
        
		return $query->result();
	}*/
	function get_CetakPenjualanBulanan3($month, $cabang, $jpenjualan)
	{
		$query = $this->db->query("select X.intid_dealer, sum(X.omsett) omsett, sum(X.omsetm) omsetm, sum(X.omsettc) omsettc, 
							sum(X.omsetlg) omsetlg,
							sum(X.omsetll) omsetll,
							X.strnama_dealer, 
							X.strnama_upline,
							X.intid_dealer,
							X.strnama_cabang, X.strnama_unit,
							(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intid_week  in (select intid_week from week where intbulan = $month) order by intid_week ASC limit 0,1) AS datestart,
							(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week  in (select intid_week from week where intbulan = $month) order by intid_week DESC  limit 0,1)AS dateend, 
							X.intid_week, 
							sum(X.intkomisi10) intkomisi10, 
							sum(X.intkomisi20) intkomisi20, 
							sum(X.intpv) intpv, 
							sum(X.inttotal_bayar) inttotal_bayar,
							sum(X.inttotal_omset) inttotal_omset, 
							X.strnama_jpenjualan, 
							X.intid_jpenjualan,
							kodebwtmisahinomsettulipdenganmetal.tradein_t,
							kodebwtmisahinomsettulipdenganmetal.tradein_m
							from 
					(select  
						(select sum( if(inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
							from nota
							where intid_week  in (select intid_week from week where intbulan = $month)
							and intid_cabang = $cabang
							and intid_jpenjualan = $jpenjualan
							and is_arisan = 0
							and a.is_dp = 0
							and a.is_arisan = 0
							and intid_nota = a.intid_nota) 
							from nota, nota_detail, barang 
							where nota.intid_nota = nota_detail.intid_nota
							and nota_detail.intid_barang = barang.intid_barang
							and nota.intid_week  in (select intid_week from week where intbulan = $month)
							and nota.intid_cabang = $cabang
							and nota.intid_jpenjualan =  $jpenjualan
							and nota_detail.is_free = 0
							and barang.intid_jbarang=1
							and nota.is_arisan = 0
							and nota.is_dp = 0
							and nota.intid_nota = a.intid_nota) as omsett,
						(select sum( if(inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
						from nota
						where intid_week  in (select intid_week from week where intbulan = $month)
						and intid_cabang = $cabang
						and intid_jpenjualan = $jpenjualan
						and is_arisan = 0
						and a.is_dp = 0
						and a.is_arisan = 0
						and intid_nota = a.intid_nota) 
						from nota, nota_detail, barang 
						where nota.intid_nota = nota_detail.intid_nota
						and nota_detail.intid_barang = barang.intid_barang
						and nota.intid_week  in (select intid_week from week where intbulan = $month)
						and nota.intid_cabang = $cabang
						and nota.intid_jpenjualan =  $jpenjualan
						and nota_detail.is_free = 0
						and barang.intid_jbarang=2
						and nota.is_arisan = 0
						and nota.is_dp = 0
						and nota.intid_nota = a.intid_nota) as omsetm,
						(select sum( if(inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
								from nota
								where intid_week  in (select intid_week from week where intbulan = $month)
								and intid_cabang = $cabang
								and intid_jpenjualan = $jpenjualan
								and is_arisan = 0
								and a.is_dp = 0
								and a.is_arisan = 0
								and intid_nota = a.intid_nota) 
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week  in (select intid_week from week where intbulan = $month)
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=3
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota
							) as omsettc,
							(select sum(nota_detail.intquantity*nota_detail.intharga)
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week  in (select intid_week from week where intbulan = $month)
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=5
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota
							) AS omsetlg, 
							(select sum(nota_detail.intquantity*nota_detail.intharga)
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week  in (select intid_week from week where intbulan = $month)
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=6
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota
							) AS omsetll,
							m.strnama_dealer, 
							m.strnama_upline,
							m.intid_dealer,
							cb.strnama_cabang, 
							u.strnama_unit,
							'".$month."' AS dateend, '".$month."' AS datestart, 
							a.intid_week, 
							(select strnama_jpenjualan from jenis_penjualan where intid_jpenjualan = $jpenjualan) as strnama_jpenjualan, 
							a.intid_jpenjualan,
							a.intpv,
							a.inttotal_omset,
							a.inttotal_bayar,
							a.intkomisi10,
							a.intkomisi20
						from nota a inner join nota_detail b on 
									a.intid_nota = b.intid_nota 
								inner join barang c on 
									b.intid_barang = c.intid_barang
								inner join member m on 
									m.intid_dealer = a.intid_dealer 
								inner join unit u on 
									u.intid_unit = a.intid_unit
								inner join cabang cb on 
									cb.intid_cabang = a.intid_cabang
						where  a.intid_week  in (select intid_week from week where intbulan = $month)
						and a.intid_cabang = $cabang
						and a.intid_jpenjualan = $jpenjualan
						and b.is_free = 0
						and a.is_dp = 0
						and a.is_arisan = 0 group by a.intid_nota) AS X 
			LEFT JOIN
				(SELECT jbarangt.intid_dealer, jbarangt.hasil AS tradein_t, jbarangm.hasil AS tradein_m FROM 
	(SELECT * FROM 
		(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
		FROM `nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
		INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
		WHERE intid_week  in (select intid_week from week where intbulan = $month) AND intid_jpenjualan = $jpenjualan
			GROUP BY intid_dealer, intid_jbarang,intid_cabang) AS asd 
	WHERE intid_jbarang = 1) AS jbarangt 
INNER JOIN 
	(SELECT * FROM 
		(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
		FROM `nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
		INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
		WHERE intid_week  in (select intid_week from week where intbulan = $month) AND intid_jpenjualan = $jpenjualan 
		GROUP BY intid_dealer, intid_jbarang) AS asd 
	WHERE intid_jbarang = 2) AS jbarangm 
ON jbarangt.intid_dealer = jbarangm.intid_dealer
where jbarangt.intid_cabang = $cabang
	) AS kodebwtmisahinomsettulipdenganmetal
	on X.intid_dealer = kodebwtmisahinomsettulipdenganmetal.intid_dealer
						group by X.intid_dealer");
        
		return $query->result();
	}
	//tgl 16 agustus 2013
	function get_CetakPenjualanMingguan_lama($week, $cabang, $jpenjualan)
	{
	/*
		select X.intid_dealer, 
							sum(X.omsett) omsett, 
							sum(X.omsetm) omsetm, 
							sum(X.omsettc) omsettc, 
							sum(X.omsetlg) omsetlg,
							sum(X.omsetll) omsetll,
							X.strnama_dealer, 
							X.strnama_upline,
							X.intid_dealer,
							X.strnama_cabang, X.strnama_unit,
							(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intid_week = $week) AS datestart,
							(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week = $week) AS dateend, 
							X.intid_week, 
							sum(X.intkomisi10) intkomisi10, 
							sum(X.intkomisi20) intkomisi20, 
							sum(X.intpv) intpv, 
							sum(X.inttotal_bayar) inttotal_bayar,
							sum(X.inttotal_omset) inttotal_omset, 
							X.strnama_jpenjualan, 
							X.intid_jpenjualan,
							kodebwtmisahinomsettulipdenganmetal.tradein_t,
							kodebwtmisahinomsettulipdenganmetal.tradein_m
							from 
					(select 
						IF(Z.omsett >= Z.v, Z.omsett - Z.v, IF(Z.omsetm < Z.omsett AND Z.omsettc < Z.omsett AND Z.omsett < v, 0, Z.omsett)) AS omsett,
						IF(Z.omsett < v AND Z.omsettc < Z.v, Z.omsetm - Z.v, IF(Z.omsett < Z.omsetm AND Z.omsettc < Z.omsetm AND Z.omsetm < Z.v, 0, Z.omsetm)) AS omsetm,
						IF(Z.omsett < v AND Z.omsettc >= Z.v, Z.omsettc - Z.v, IF(Z.omsett < Z.omsettc AND Z.omsetm < Z.omsettc AND Z.omsettc < Z.v, 0, Z.omsettc)) AS omsettc, 
						Z.omsetlg,
						Z.omsetll,
						Z.strnama_dealer,
						Z.strnama_upline,
						Z.intid_dealer,
						Z.strnama_cabang, 
						Z.strnama_unit
						Z.intid_week, 
						Z.strnama_jpenjualan, 
						Z.intid_jpenjualan,
						Z.intpv,
						Z.inttotal_omset,
						Z.inttotal_bayar,
						Z.intkomisi10,
						Z.intkomisi20
						from 
						(select  
							(select sum( nota_detail.intquantity*nota_detail.intharga)
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week = $week
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=1
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota) as omsett,
							(select sum( nota_detail.intquantity*nota_detail.intharga)
							from nota, nota_detail, barang 
							where nota.intid_nota = nota_detail.intid_nota
							and nota_detail.intid_barang = barang.intid_barang
							and nota.intid_week = $week
							and nota.intid_cabang = $cabang
							and nota.intid_jpenjualan =  $jpenjualan
							and nota_detail.is_free = 0
							and barang.intid_jbarang=2
							and nota.is_arisan = 0
							and nota.is_dp = 0
							and nota.intid_nota = a.intid_nota) as omsetm,
							(select sum(nota_detail.intquantity*nota_detail.intharga)
									from nota, nota_detail, barang 
									where nota.intid_nota = nota_detail.intid_nota
									and nota_detail.intid_barang = barang.intid_barang
									and nota.intid_week = $week
									and nota.intid_cabang = $cabang
									and nota.intid_jpenjualan =  $jpenjualan
									and nota_detail.is_free = 0
									and barang.intid_jbarang=3
									and nota.is_arisan = 0
									and nota.is_dp = 0
									and nota.intid_nota = a.intid_nota
								) as omsettc,
								(select sum(nota_detail.intquantity*nota_detail.intharga)
									from nota, nota_detail, barang 
									where nota.intid_nota = nota_detail.intid_nota
									and nota_detail.intid_barang = barang.intid_barang
									and nota.intid_week = $week
									and nota.intid_cabang = $cabang
									and nota.intid_jpenjualan =  $jpenjualan
									and nota_detail.is_free = 0
									and barang.intid_jbarang=5
									and nota.is_arisan = 0
									and nota.is_dp = 0
									and nota.intid_nota = a.intid_nota
								) AS omsetlg, 
								(select sum(nota_detail.intquantity*nota_detail.intharga)
									from nota, nota_detail, barang 
									where nota.intid_nota = nota_detail.intid_nota
									and nota_detail.intid_barang = barang.intid_barang
									and nota.intid_week = $week
									and nota.intid_cabang = $cabang
									and nota.intid_jpenjualan =  $jpenjualan
									and nota_detail.is_free = 0
									and barang.intid_jbarang=6
									and nota.is_arisan = 0
									and nota.is_dp = 0
									and nota.intid_nota = a.intid_nota
								) AS omsetll,
								(select sum(intvoucher)
									from nota
									where intid_week = $week
									and intid_cabang = $cabang
									and intid_jpenjualan = $jpenjualan
									and is_arisan = 0
									and is_dp = 0
									and is_arisan = 0
									and intid_nota = a.intid_nota
								) AS v,
								m.strnama_dealer, 
								m.strnama_upline,
								m.intid_dealer,
								cb.strnama_cabang, 
								u.strnama_unit
								a.intid_week, 
								(select strnama_jpenjualan from jenis_penjualan where intid_jpenjualan = $jpenjualan) as strnama_jpenjualan, 
								a.intid_jpenjualan,
								a.intpv,
								a.inttotal_omset,
								a.inttotal_bayar,
								a.intkomisi10,
								a.intkomisi20
							from nota a inner join nota_detail b on 
										a.intid_nota = b.intid_nota 
									inner join barang c on 
										b.intid_barang = c.intid_barang
									inner join member m on 
										m.intid_dealer = a.intid_dealer 
									inner join unit u on 
										u.intid_unit = a.intid_unit
									inner join cabang cb on 
										cb.intid_cabang = a.intid_cabang
							where  a.intid_week = $week
							and a.intid_cabang = $cabang
							and a.intid_jpenjualan = $jpenjualan
							and b.is_free = 0
							and a.is_dp = 0
							and a.is_arisan = 0 group by a.intid_nota) AS Z 
							) AS X
			LEFT JOIN
				(SELECT jbarangt.intid_dealer, jbarangt.hasil AS tradein_t, jbarangm.hasil AS tradein_m FROM 
					(SELECT * FROM 
						(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
						FROM `nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
						INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
						WHERE intid_week = $week AND intid_jpenjualan = $jpenjualan
										AND intid_cabang = $cabang
							GROUP BY intid_dealer, intid_jbarang,intid_cabang) AS asd 
					WHERE intid_jbarang = 1) AS jbarangt 
INNER JOIN 
	(SELECT * FROM 
		(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
		FROM 
		`nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
		INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
		WHERE 
			intid_week = $week AND intid_jpenjualan = $jpenjualan 
										AND intid_cabang = $cabang
		GROUP BY intid_dealer, intid_jbarang) AS asd 
	WHERE intid_jbarang = 2) AS jbarangm 
ON jbarangt.intid_dealer = jbarangm.intid_dealer
where jbarangt.intid_cabang = $cabang
	) AS kodebwtmisahinomsettulipdenganmetal
	on X.intid_dealer = kodebwtmisahinomsettulipdenganmetal.intid_dealer
						group by X.intid_dealer
	*/
		$query = $this->db->query("select X.intid_dealer, sum(X.omsett) omsett, sum(X.omsetm) omsetm, sum(X.omsettc) omsettc, 
							sum(X.omsetlg) omsetlg,
							sum(X.omsetll) omsetll,
							X.strnama_dealer, 
							X.strnama_upline,
							X.intid_dealer,
							X.strnama_cabang, X.strnama_unit,
							(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intid_week = $week) AS datestart,
							(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week = $week) AS dateend, 
							X.intid_week, 
							sum(X.intkomisi10) intkomisi10, 
							sum(X.intkomisi20) intkomisi20, 
							sum(X.intpv) intpv, 
							sum(X.inttotal_bayar) inttotal_bayar,
							sum(X.inttotal_omset) inttotal_omset, 
							X.strnama_jpenjualan, 
							X.intid_jpenjualan,
							kodebwtmisahinomsettulipdenganmetal.tradein_t,
							kodebwtmisahinomsettulipdenganmetal.tradein_m
							from 
					(select  
						(select sum( if(inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
							from nota
							where intid_week = $week
							and intid_cabang = $cabang
							and intid_jpenjualan = $jpenjualan
							and is_arisan = 0
							and a.is_dp = 0
							and a.is_arisan = 0
							and intid_nota = a.intid_nota) 
							from nota, nota_detail, barang 
							where nota.intid_nota = nota_detail.intid_nota
							and nota_detail.intid_barang = barang.intid_barang
							and nota.intid_week = $week
							and nota.intid_cabang = $cabang
							and nota.intid_jpenjualan =  $jpenjualan
							and nota_detail.is_free = 0
							and barang.intid_jbarang=1
							and nota.is_arisan = 0
							and nota.is_dp = 0
							and nota.intid_nota = a.intid_nota) as omsett,
						(select sum( if(inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
						from nota
						where intid_week = $week
						and intid_cabang = $cabang
						and intid_jpenjualan = $jpenjualan
						and is_arisan = 0
						and a.is_dp = 0
						and a.is_arisan = 0
						and intid_nota = a.intid_nota) 
						from nota, nota_detail, barang 
						where nota.intid_nota = nota_detail.intid_nota
						and nota_detail.intid_barang = barang.intid_barang
						and nota.intid_week = $week
						and nota.intid_cabang = $cabang
						and nota.intid_jpenjualan =  $jpenjualan
						and nota_detail.is_free = 0
						and barang.intid_jbarang=2
						and nota.is_arisan = 0
						and nota.is_dp = 0
						and nota.intid_nota = a.intid_nota) as omsetm,
						(select sum( if(inttotal_omset = 0, 0, (nota_detail.intquantity*nota_detail.intharga)))- (select sum(if(inttotal_omset =0, 0, intvoucher))
								from nota
								where intid_week = $week
								and intid_cabang = $cabang
								and intid_jpenjualan = $jpenjualan
								and is_arisan = 0
								and a.is_dp = 0
								and a.is_arisan = 0
								and intid_nota = a.intid_nota) 
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week = $week
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=3
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota
							) as omsettc,
							(select sum(nota_detail.intquantity*nota_detail.intharga)
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week = $week
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=5
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota
							) AS omsetlg, 
							(select sum(nota_detail.intquantity*nota_detail.intharga)
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week = $week
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=6
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota
							) AS omsetll,
							m.strnama_dealer, 
							m.strnama_upline,
							m.intid_dealer,
							cb.strnama_cabang, 
							u.strnama_unit,
							'".$week."' AS dateend, '".$week."' AS datestart, 
							a.intid_week, 
							(select strnama_jpenjualan from jenis_penjualan where intid_jpenjualan = $jpenjualan) as strnama_jpenjualan, 
							a.intid_jpenjualan,
							a.intpv,
							a.inttotal_omset,
							a.inttotal_bayar,
							a.intkomisi10,
							a.intkomisi20
						from nota a inner join nota_detail b on 
									a.intid_nota = b.intid_nota 
								inner join barang c on 
									b.intid_barang = c.intid_barang
								inner join member m on 
									m.intid_dealer = a.intid_dealer 
								inner join unit u on 
									u.intid_unit = a.intid_unit
								inner join cabang cb on 
									cb.intid_cabang = a.intid_cabang
						where  a.intid_week = $week
						and a.intid_cabang = $cabang
						and a.intid_jpenjualan = $jpenjualan
						and b.is_free = 0
						and a.is_dp = 0
						and a.is_arisan = 0 group by a.intid_nota) AS X 
			LEFT JOIN
				(SELECT jbarangt.intid_dealer, jbarangt.hasil AS tradein_t, jbarangm.hasil AS tradein_m FROM 
					(SELECT * FROM 
						(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
						FROM `nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
						INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
						WHERE intid_week = $week AND intid_jpenjualan = $jpenjualan
										AND intid_cabang = $cabang
							GROUP BY intid_dealer, intid_jbarang,intid_cabang) AS asd 
					WHERE intid_jbarang = 1) AS jbarangt 
INNER JOIN 
	(SELECT * FROM 
		(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
		FROM 
		`nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
		INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
		WHERE 
			intid_week = $week AND intid_jpenjualan = $jpenjualan 
										AND intid_cabang = $cabang
		GROUP BY intid_dealer, intid_jbarang) AS asd 
	WHERE intid_jbarang = 2) AS jbarangm 
ON jbarangt.intid_dealer = jbarangm.intid_dealer
where jbarangt.intid_cabang = $cabang
	) AS kodebwtmisahinomsettulipdenganmetal
	on X.intid_dealer = kodebwtmisahinomsettulipdenganmetal.intid_dealer
						group by X.intid_dealer");
        
		return $query->result();
	}
	
	//end
	function get_periode($data){
			if(isset($data['week'])){
					$select = "select (select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intid_week =".$data['week'].") AS datestart, (select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week = ".$data['week'].") AS dateend";
			}elseif(isset($data['month'])){
					$select = "select (select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intbulan =".$data['month']." order by intid_week asc limit 0,1) AS datestart, (select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intbulan = ".$data['month']." order by intid_week DESC limit 0,1) AS dateend";
			}
			$query = $this->db->query($select);
			return $query->result();
		}
		
	//tgl 19 agustus 2013
		function get_CetakPenjualanMingguan($week, $cabang, $jpenjualan)
	{
		$query = $this->db->query("select X.intid_dealer, 
							sum(X.omsett) omsett, 
							sum(X.omsetm) omsetm, 
							sum(X.omsettc) omsettc, 
							sum(X.omsetlg) omsetlg,
							sum(X.omsetll) omsetll,
							X.strnama_dealer, 
							X.strnama_upline,
							X.intid_dealer,
							X.strnama_cabang, X.strnama_unit,
							(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intid_week = $week) AS datestart,
							(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week = $week) AS dateend, 
							X.intid_week, 
							sum(X.intkomisi10) intkomisi10, 
							sum(X.intkomisi20) intkomisi20, 
							sum(X.intpv) intpv, 
							sum(X.inttotal_bayar) inttotal_bayar,
							sum(X.inttotal_omset) inttotal_omset, 
							X.strnama_jpenjualan, 
							X.intid_jpenjualan,
							kodebwtmisahinomsettulipdenganmetal.tradein_t,
							kodebwtmisahinomsettulipdenganmetal.tradein_m
							from 
					(select 
						IF(Z.omsett >= Z.v, Z.omsett - Z.v, IF(Z.omsetm < Z.omsett AND Z.omsettc < Z.omsett AND Z.omsett < v , 0, Z.omsett)) AS omsett,
						IF(Z.omsett < v AND Z.omsettc < Z.v AND Z.omsetm >= Z.v, Z.omsetm - Z.v, IF(Z.omsett < Z.omsetm AND Z.omsettc < Z.omsetm AND Z.omsetm < Z.v, 0, Z.omsetm)) AS omsetm,
						IF(Z.omsett < v AND Z.omsettc >= Z.v, Z.omsettc - Z.v, IF(Z.omsett < Z.omsettc AND Z.omsetm < Z.omsettc AND Z.omsettc < Z.v, 0, Z.omsettc)) AS omsettc, 
						Z.omsetlg,
						Z.omsetll,
						Z.strnama_dealer,
						Z.strnama_upline,
						Z.intid_dealer,
						Z.strnama_cabang, 
						Z.strnama_unit,
						Z.intid_week, 
						Z.strnama_jpenjualan, 
						Z.intid_jpenjualan,
						IF(Z.intpv < 0, 0, Z.intpv)AS intpv,
						IF(Z.inttotal_omset < 0, 0, Z.inttotal_omset) AS inttotal_omset,
						IF(Z.inttotal_bayar < 0, 0, Z.inttotal_bayar) AS inttotal_bayar,
						IF(Z.intkomisi10 < 0, 0, Z.intkomisi10)AS intkomisi10,
						IF(Z.intkomisi20 < 0, 0, Z.intkomisi20)AS intkomisi20
						from 
						(select  
							(select  if(sum( nota_detail.intquantity*nota_detail.intharga) IS NULL,0,sum( nota_detail.intquantity*nota_detail.intharga))
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week = $week
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=1
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota) as omsett,
							(select  if(sum( nota_detail.intquantity*nota_detail.intharga) IS NULL,0,sum( nota_detail.intquantity*nota_detail.intharga))
							from nota, nota_detail, barang 
							where nota.intid_nota = nota_detail.intid_nota
							and nota_detail.intid_barang = barang.intid_barang
							and nota.intid_week = $week
							and nota.intid_cabang = $cabang
							and nota.intid_jpenjualan =  $jpenjualan
							and nota_detail.is_free = 0
							and barang.intid_jbarang=2
							and nota.is_arisan = 0
							and nota.is_dp = 0
							and nota.intid_nota = a.intid_nota) as omsetm,
							(select  if(sum( nota_detail.intquantity*nota_detail.intharga) IS NULL,0,sum( nota_detail.intquantity*nota_detail.intharga))
									from nota, nota_detail, barang 
									where nota.intid_nota = nota_detail.intid_nota
									and nota_detail.intid_barang = barang.intid_barang
									and nota.intid_week = $week
									and nota.intid_cabang = $cabang
									and nota.intid_jpenjualan =  $jpenjualan
									and nota_detail.is_free = 0
									and barang.intid_jbarang=3
									and nota.is_arisan = 0
									and nota.is_dp = 0
									and nota.intid_nota = a.intid_nota
								) as omsettc,
								(select sum(nota_detail.intquantity*nota_detail.intharga)
									from nota, nota_detail, barang 
									where nota.intid_nota = nota_detail.intid_nota
									and nota_detail.intid_barang = barang.intid_barang
									and nota.intid_week = $week
									and nota.intid_cabang = $cabang
									and nota.intid_jpenjualan =  $jpenjualan
									and nota_detail.is_free = 0
									and barang.intid_jbarang=5
									and nota.is_arisan = 0
									and nota.is_dp = 0
									and nota.intid_nota = a.intid_nota
								) AS omsetlg, 
								(select sum(nota_detail.intquantity*nota_detail.intharga)
									from nota, nota_detail, barang 
									where nota.intid_nota = nota_detail.intid_nota
									and nota_detail.intid_barang = barang.intid_barang
									and nota.intid_week = $week
									and nota.intid_cabang = $cabang
									and nota.intid_jpenjualan =  $jpenjualan
									and nota_detail.is_free = 0
									and barang.intid_jbarang=6
									and nota.is_arisan = 0
									and nota.is_dp = 0
									and nota.intid_nota = a.intid_nota
								) AS omsetll,
								(select sum(intvoucher)
									from nota
									where intid_week = $week
									and intid_cabang = $cabang
									and intid_jpenjualan = $jpenjualan
									and is_arisan = 0
									and is_dp = 0
									and is_arisan = 0
									and intid_nota = a.intid_nota
								) AS v,
								m.strnama_dealer, 
								m.strnama_upline,
								m.intid_dealer,
								cb.strnama_cabang, 
								u.strnama_unit,
								a.intid_week, 
								(select strnama_jpenjualan from jenis_penjualan where intid_jpenjualan = $jpenjualan) as strnama_jpenjualan, 
								a.intid_jpenjualan,
								a.intpv,
								a.inttotal_omset,
								a.inttotal_bayar,
								a.intkomisi10,
								a.intkomisi20
							from nota a inner join nota_detail b on 
										a.intid_nota = b.intid_nota 
									inner join barang c on 
										b.intid_barang = c.intid_barang
									inner join member m on 
										m.intid_dealer = a.intid_dealer 
									inner join unit u on 
										u.intid_unit = a.intid_unit
									inner join cabang cb on 
										cb.intid_cabang = a.intid_cabang
							where  a.intid_week = $week
							and a.intid_cabang = $cabang
							and a.intid_jpenjualan = $jpenjualan
							and b.is_free = 0
							and a.is_dp = 0
							and a.is_arisan = 0 group by a.intid_nota) AS Z 
							) AS X
			LEFT JOIN
				(SELECT jbarangt.intid_dealer, jbarangt.hasil AS tradein_t, jbarangm.hasil AS tradein_m FROM 
					(SELECT * FROM 
						(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
						FROM `nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
						INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
						WHERE intid_week = $week AND intid_jpenjualan = $jpenjualan
										AND intid_cabang = $cabang
							GROUP BY intid_dealer, intid_jbarang,intid_cabang) AS asd 
					WHERE intid_jbarang = 1) AS jbarangt 
INNER JOIN 
	(SELECT * FROM 
		(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
		FROM 
		`nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
		INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
		WHERE 
			intid_week = $week AND intid_jpenjualan = $jpenjualan 
										AND intid_cabang = $cabang
		GROUP BY intid_dealer, intid_jbarang) AS asd 
	WHERE intid_jbarang = 2) AS jbarangm 
ON jbarangt.intid_dealer = jbarangm.intid_dealer
where jbarangt.intid_cabang = $cabang
	) AS kodebwtmisahinomsettulipdenganmetal
	on X.intid_dealer = kodebwtmisahinomsettulipdenganmetal.intid_dealer
						group by X.intid_dealer");
        
		return $query->result();
		}
		function get_CetakPenjualanBulanan($month, $cabang, $jpenjualan)
	{
		$query = $this->db->query("select 
					X.intid_dealer, 
					sum(X.omsett) omsett, 
					sum(X.omsetm) omsetm, sum(X.omsettc)omsettc, 
							sum(X.omsetlg) omsetlg,
							sum(X.omsetll) omsetll,
							X.strnama_dealer, 
							X.strnama_upline,
							X.intid_dealer,
							X.strnama_cabang, X.strnama_unit,
							(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intbulan = $month order by intid_week ASC limit 0,1) AS datestart,
							(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intbulan = $month order by intid_week DESC limit 0,1) AS dateend, 
							X.intid_week, 
							sum(X.intkomisi10) intkomisi10, 
							sum(X.intkomisi20) intkomisi20, 
							sum(X.intpv) intpv, 
							sum(X.inttotal_bayar) inttotal_bayar,
							sum(X.inttotal_omset) inttotal_omset, 
							X.strnama_jpenjualan, 
							X.intid_jpenjualan,
							'".$month."' AS intbulan,
							kodebwtmisahinomsettulipdenganmetal.tradein_t,
							kodebwtmisahinomsettulipdenganmetal.tradein_m
							from 
					(select 
						IF(Z.omsett >= Z.v, Z.omsett - Z.v, IF(Z.omsetm < Z.omsett AND Z.omsettc < Z.omsett AND Z.omsett < v , 0, Z.omsett)) AS omsett,
						IF(Z.omsett < v AND Z.omsettc < Z.v AND Z.omsetm >= Z.v, Z.omsetm - Z.v, IF(Z.omsett < Z.omsetm AND Z.omsettc < Z.omsetm AND Z.omsetm < Z.v, 0, Z.omsetm)) AS omsetm,
						IF(Z.omsett < v AND Z.omsettc >= Z.v, Z.omsettc - Z.v, IF(Z.omsett < Z.omsettc AND Z.omsetm < Z.omsettc AND Z.omsettc < Z.v, 0, Z.omsettc)) AS omsettc, 
						Z.omsetlg,
						Z.omsetll,
						Z.strnama_dealer,
						Z.strnama_upline,
						Z.intid_dealer,
						Z.strnama_cabang, 
						Z.strnama_unit,
						Z.intid_week, 
						Z.strnama_jpenjualan, 
						Z.intid_jpenjualan,
						IF(Z.intpv < 0, 0, Z.intpv)AS intpv,
						IF(Z.inttotal_omset < 0, 0, Z.inttotal_omset) AS inttotal_omset,
						IF(Z.inttotal_bayar < 0, 0, Z.inttotal_bayar) AS inttotal_bayar,
						IF(Z.intkomisi10 < 0, 0, Z.intkomisi10)AS intkomisi10,
						IF(Z.intkomisi20 < 0, 0, Z.intkomisi20)AS intkomisi20
						from 
						(select  
							(select  if(sum( nota_detail.intquantity*nota_detail.intharga) IS NULL,0,sum( nota_detail.intquantity*nota_detail.intharga))
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week  in (select intid_week from week where intbulan = $month)
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=1
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota) as omsett,
							(select  if(sum( nota_detail.intquantity*nota_detail.intharga) IS NULL,0,sum( nota_detail.intquantity*nota_detail.intharga))
							from nota, nota_detail, barang 
							where nota.intid_nota = nota_detail.intid_nota
							and nota_detail.intid_barang = barang.intid_barang
							and nota.intid_week  in (select intid_week from week where intbulan = $month)
							and nota.intid_cabang = $cabang
							and nota.intid_jpenjualan =  $jpenjualan
							and nota_detail.is_free = 0
							and barang.intid_jbarang=2
							and nota.is_arisan = 0
							and nota.is_dp = 0
							and nota.intid_nota = a.intid_nota) as omsetm,
							(select  if(sum( nota_detail.intquantity*nota_detail.intharga) IS NULL,0,sum( nota_detail.intquantity*nota_detail.intharga))
									from nota, nota_detail, barang 
									where nota.intid_nota = nota_detail.intid_nota
									and nota_detail.intid_barang = barang.intid_barang
									and nota.intid_week  in (select intid_week from week where intbulan = $month)
									and nota.intid_cabang = $cabang
									and nota.intid_jpenjualan =  $jpenjualan
									and nota_detail.is_free = 0
									and barang.intid_jbarang=3
									and nota.is_arisan = 0
									and nota.is_dp = 0
									and nota.intid_nota = a.intid_nota
								) as omsettc,
								(select sum(nota_detail.intquantity*nota_detail.intharga)
									from nota, nota_detail, barang 
									where nota.intid_nota = nota_detail.intid_nota
									and nota_detail.intid_barang = barang.intid_barang
									and nota.intid_week  in (select intid_week from week where intbulan = $month)
									and nota.intid_cabang = $cabang
									and nota.intid_jpenjualan =  $jpenjualan
									and nota_detail.is_free = 0
									and barang.intid_jbarang=5
									and nota.is_arisan = 0
									and nota.is_dp = 0
									and nota.intid_nota = a.intid_nota
								) AS omsetlg, 
								(select sum(nota_detail.intquantity*nota_detail.intharga)
									from nota, nota_detail, barang 
									where nota.intid_nota = nota_detail.intid_nota
									and nota_detail.intid_barang = barang.intid_barang
									and nota.intid_week  in (select intid_week from week where intbulan = $month)
									and nota.intid_cabang = $cabang
									and nota.intid_jpenjualan =  $jpenjualan
									and nota_detail.is_free = 0
									and barang.intid_jbarang=6
									and nota.is_arisan = 0
									and nota.is_dp = 0
									and nota.intid_nota = a.intid_nota
								) AS omsetll,
								(select sum(intvoucher)
									from nota
									where intid_week  in (select intid_week from week where intbulan = $month)
									and intid_cabang = $cabang
									and intid_jpenjualan = $jpenjualan
									and is_arisan = 0
									and is_dp = 0
									and is_arisan = 0
									and intid_nota = a.intid_nota
								) AS v,
								m.strnama_dealer, 
								m.strnama_upline,
								m.intid_dealer,
								cb.strnama_cabang, 
								u.strnama_unit,
								a.intid_week, 
								(select strnama_jpenjualan from jenis_penjualan where intid_jpenjualan = $jpenjualan) as strnama_jpenjualan, 
								a.intid_jpenjualan,
								a.intpv,
								a.inttotal_omset,
								a.inttotal_bayar,
								a.intkomisi10,
								a.intkomisi20
							from nota a inner join nota_detail b on 
										a.intid_nota = b.intid_nota 
									inner join barang c on 
										b.intid_barang = c.intid_barang
									inner join member m on 
										m.intid_dealer = a.intid_dealer 
									inner join unit u on 
										u.intid_unit = a.intid_unit
									inner join cabang cb on 
										cb.intid_cabang = a.intid_cabang
							where  a.intid_week  in (select intid_week from week where intbulan = $month)
							and a.intid_cabang = $cabang
							and a.intid_jpenjualan = $jpenjualan
							and b.is_free = 0
							and a.is_dp = 0
							and a.is_arisan = 0 group by a.intid_nota) AS Z ) AS X 
			LEFT JOIN
				(SELECT jbarangt.intid_dealer, jbarangt.hasil AS tradein_t, jbarangm.hasil AS tradein_m FROM 
					(SELECT * FROM 
						(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
						FROM `nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
						INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
						WHERE intid_week  in (select intid_week from week where intbulan = $month) AND intid_jpenjualan = $jpenjualan and 
										is_arisan = 0
										and is_dp = 0
										AND intid_cabang = $cabang
							GROUP BY intid_dealer, intid_jbarang,intid_cabang) AS asd 
					WHERE intid_jbarang = 1) AS jbarangt 
		INNER JOIN 
				(SELECT * FROM 
					(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
					FROM `nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
					INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
					WHERE intid_week  in (select intid_week from week where intbulan = $month) AND intid_jpenjualan = $jpenjualan and is_arisan = 0
									and is_dp = 0
										AND intid_cabang = $cabang
					GROUP BY intid_dealer, intid_jbarang) AS asd 
				WHERE intid_jbarang = 2) AS jbarangm 
ON jbarangt.intid_dealer = jbarangm.intid_dealer
where jbarangt.intid_cabang = $cabang
	) AS kodebwtmisahinomsettulipdenganmetal
	on X.intid_dealer = kodebwtmisahinomsettulipdenganmetal.intid_dealer
						group by X.intid_dealer");
        return $query->result();
	}
	/*
	function get_CetakPenjualanBulanan11($month, $cabang, $jpenjualan)
	{
		$query = $this->db->query("select 
					X.intid_dealer, 
					sum(X.omsett) omsett, 
					sum(X.omsetm) omsetm, sum(X.omsettc)omsettc, 
							sum(X.omsetlg) omsetlg,
							sum(X.omsetll) omsetll,
							X.strnama_dealer, 
							X.strnama_upline,
							X.intid_dealer,
							X.strnama_cabang, X.strnama_unit,
							(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intbulan = $month order by intid_week ASC limit 0,1) AS datestart,
							(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intbulan = $month order by intid_week DESC limit 0,1) AS dateend, 
							X.intid_week, 
							sum(X.intkomisi10) intkomisi10, 
							sum(X.intkomisi20) intkomisi20, 
							sum(X.intpv) intpv, 
							sum(X.inttotal_bayar) inttotal_bayar,
							sum(X.inttotal_omset) inttotal_omset, 
							X.strnama_jpenjualan, 
							X.intid_jpenjualan,
							'".$month."' AS intbulan,
							X.tradein_t,
							X.tradein_m
							from 
					(select 
						IF(Z.omsett >= Z.v, Z.omsett - Z.v, IF(Z.omsetm < Z.omsett AND Z.omsettc < Z.omsett AND Z.omsett < v , 0, Z.omsett)) AS omsett,
						IF(Z.omsett < v AND Z.omsettc < Z.v AND Z.omsetm >= Z.v, Z.omsetm - Z.v, IF(Z.omsett < Z.omsetm AND Z.omsettc < Z.omsetm AND Z.omsetm < Z.v, 0, Z.omsetm)) AS omsetm,
						IF(Z.omsett < v AND Z.omsettc >= Z.v, Z.omsettc - Z.v, IF(Z.omsett < Z.omsettc AND Z.omsetm < Z.omsettc AND Z.omsettc < Z.v, 0, Z.omsettc)) AS omsettc, 
						Z.omsetlg,
						Z.omsetll,
						Z.strnama_dealer,
						Z.strnama_upline,
						Z.intid_dealer,
						Z.strnama_cabang, 
						Z.strnama_unit,
						Z.intid_week, 
						Z.strnama_jpenjualan, 
						Z.intid_jpenjualan,
						IF(Z.intpv < 0, 0, Z.intpv)AS intpv,
						IF(Z.inttotal_omset < 0, 0, Z.inttotal_omset) AS inttotal_omset,
						IF(Z.inttotal_bayar < 0, 0, Z.inttotal_bayar) AS inttotal_bayar,
						IF(Z.intkomisi10 < 0, 0, Z.intkomisi10)AS intkomisi10,
						IF(Z.intkomisi20 < 0, 0, Z.intkomisi20)AS intkomisi20
						from 
						(select  
							(select  if(sum( nota_detail.intquantity*nota_detail.intharga) IS NULL,0,sum( nota_detail.intquantity*nota_detail.intharga))
								from nota, nota_detail, barang 
								where nota.intid_nota = nota_detail.intid_nota
								and nota_detail.intid_barang = barang.intid_barang
								and nota.intid_week  in (select intid_week from week where intbulan = $month)
								and nota.intid_cabang = $cabang
								and nota.intid_jpenjualan =  $jpenjualan
								and nota_detail.is_free = 0
								and barang.intid_jbarang=1
								and nota.is_arisan = 0
								and nota.is_dp = 0
								and nota.intid_nota = a.intid_nota) as omsett,
							(select  if(sum( nota_detail.intquantity*nota_detail.intharga) IS NULL,0,sum( nota_detail.intquantity*nota_detail.intharga))
							from nota, nota_detail, barang 
							where nota.intid_nota = nota_detail.intid_nota
							and nota_detail.intid_barang = barang.intid_barang
							and nota.intid_week  in (select intid_week from week where intbulan = $month)
							and nota.intid_cabang = $cabang
							and nota.intid_jpenjualan =  $jpenjualan
							and nota_detail.is_free = 0
							and barang.intid_jbarang=2
							and nota.is_arisan = 0
							and nota.is_dp = 0
							and nota.intid_nota = a.intid_nota) as omsetm,
							(select  if(sum( nota_detail.intquantity*nota_detail.intharga) IS NULL,0,sum( nota_detail.intquantity*nota_detail.intharga))
									from nota, nota_detail, barang 
									where nota.intid_nota = nota_detail.intid_nota
									and nota_detail.intid_barang = barang.intid_barang
									and nota.intid_week  in (select intid_week from week where intbulan = $month)
									and nota.intid_cabang = $cabang
									and nota.intid_jpenjualan =  $jpenjualan
									and nota_detail.is_free = 0
									and barang.intid_jbarang=3
									and nota.is_arisan = 0
									and nota.is_dp = 0
									and nota.intid_nota = a.intid_nota
								) as omsettc,
								(select sum(nota_detail.intquantity*nota_detail.intharga)
									from nota, nota_detail, barang 
									where nota.intid_nota = nota_detail.intid_nota
									and nota_detail.intid_barang = barang.intid_barang
									and nota.intid_week  in (select intid_week from week where intbulan = $month)
									and nota.intid_cabang = $cabang
									and nota.intid_jpenjualan =  $jpenjualan
									and nota_detail.is_free = 0
									and barang.intid_jbarang=5
									and nota.is_arisan = 0
									and nota.is_dp = 0
									and nota.intid_nota = a.intid_nota
								) AS omsetlg, 
								(select sum(nota_detail.intquantity*nota_detail.intharga)
									from nota, nota_detail, barang 
									where nota.intid_nota = nota_detail.intid_nota
									and nota_detail.intid_barang = barang.intid_barang
									and nota.intid_week  in (select intid_week from week where intbulan = $month)
									and nota.intid_cabang = $cabang
									and nota.intid_jpenjualan =  $jpenjualan
									and nota_detail.is_free = 0
									and barang.intid_jbarang=6
									and nota.is_arisan = 0
									and nota.is_dp = 0
									and nota.intid_nota = a.intid_nota
								) AS omsetll,
								(select sum(intvoucher)
									from nota
									where intid_week  in (select intid_week from week where intbulan = $month)
									and intid_cabang = $cabang
									and intid_jpenjualan = $jpenjualan
									and is_arisan = 0
									and is_dp = 0
									and is_arisan = 0
									and intid_nota = a.intid_nota
								) AS v,
								m.strnama_dealer, 
								m.strnama_upline,
								m.intid_dealer,
								cb.strnama_cabang, 
								u.strnama_unit,
								a.intid_week, 
								(select strnama_jpenjualan from jenis_penjualan where intid_jpenjualan = $jpenjualan) as strnama_jpenjualan, 
								a.intid_jpenjualan,
								a.intpv,
								a.inttotal_omset,
								a.inttotal_bayar,
								a.intkomisi10,
								a.intkomisi20
							from nota a inner join nota_detail b on 
										a.intid_nota = b.intid_nota 
									inner join barang c on 
										b.intid_barang = c.intid_barang
									inner join member m on 
										m.intid_dealer = a.intid_dealer 
									inner join unit u on 
										u.intid_unit = a.intid_unit
									inner join cabang cb on 
										cb.intid_cabang = a.intid_cabang
							where  a.intid_week  in (select intid_week from week where intbulan = $month)
							and a.intid_cabang = $cabang
							and a.intid_jpenjualan = $jpenjualan
							and b.is_free = 0
							and a.is_dp = 0
							and a.is_arisan = 0 group by a.intid_nota
						) AS Z 
						LEFT JOIN
						(SELECT jbarangt.intid_nota, jbarangt.intid_dealer, jbarangt.hasil AS tradein_t, jbarangm.hasil AS tradein_m FROM 
								(SELECT * FROM 
									(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
									FROM `nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
									INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
									WHERE intid_week  in (select intid_week from week where intbulan = $month) AND intid_jpenjualan = $jpenjualan and 
													is_arisan = 0
													and is_dp = 0
													AND intid_cabang = $cabang
										GROUP BY intid_dealer, intid_jbarang,intid_cabang) AS asd 
								WHERE intid_jbarang = 1) AS jbarangt 
						INNER JOIN 
							(SELECT * FROM 
								(SELECT intid_dealer, barang.intid_jbarang, SUM(intharga * intquantity * (1 - (inttrade_in * 0.01))) AS hasil, intid_cabang 
								FROM `nota` INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota 
								INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang 
								WHERE intid_week  in (select intid_week from week where intbulan = $month) AND intid_jpenjualan = $jpenjualan and is_arisan = 0
												and is_dp = 0
													AND intid_cabang = $cabang
								GROUP BY intid_dealer, intid_jbarang) AS asd 
							WHERE intid_jbarang = 2) AS jbarangm 
						ON jbarangt.intid_nota = jbarangm.intid_nota
						where jbarangt.intid_cabang = $cabang
				) AS kodebwtmisahinomsettulipdenganmetal
	on Z.intid_nota = kodebwtmisahinomsettulipdenganmetal.intid_nota
					) AS X 
			
						group by X.intid_dealer");
        return $query->result();
	}
	*/
		function get_CetakKeuanganMingguanReguler($cabang, $week)
	{
		$query = $this->db->query("SELECT n AS intno_nota, 
												IF(ot >= v AND ot IS NOT NULL, ot - v, IF(om < ot AND otc < ot AND ot < v , 0, ot)) AS omsettulip,
												IF(ot < v AND otc < v AND om >= v AND om IS NOT NULL, om - v, IF(ot < om AND otc < om AND om < v, 0, om)) AS omsetmetal,
												IF(ot  < v AND otc >= v AND otc IS NOT NULL, otc - v, IF(ot < otc AND om < otc AND otc < v, 0, otc)) AS omsettc
										FROM 
										(SELECT 
											IF(dt IS NULL, IF(dm IS NULL,dtc,dm), dt) AS d, 
											IF(nt IS NULL, IF(nm IS NULL,ntc,nm), nt) AS n, 
											ot, 
											om, 
											otc 
											FROM 
												(SELECT * 
													FROM 
														(SELECT 
															intid_dealer AS dt, 
															intno_nota AS nt, 
															SUM(totalharga) AS ot
															FROM 
																(SELECT 
																	intid_dealer, 
																	intno_nota, 
																	SUM(intquantity * intharga) AS totalharga, 
																	intid_jbarang 
																	FROM 
																		nota a 
																			INNER JOIN nota_detail ON a.intid_nota = nota_detail.intid_nota 
																			INNER JOIN barang ON barang.intid_barang = nota_detail.intid_barang 
																	WHERE 
																		a.intid_week = $week 
																		AND a.intid_cabang = $cabang 
																		AND a.intid_jpenjualan = 1 
																		AND a.is_arisan = 0 
																		AND a.is_dp = 0 
																		AND a.inttotal_bayar > 0
																		AND nota_detail.is_free = 0 
																		GROUP BY intno_nota, intid_jbarang
																) AS z 
															WHERE 
															intid_jbarang = 1 
															
															GROUP BY intno_nota
														) AS omsettulip 
														LEFT JOIN 
														(SELECT 
															intid_dealer AS dm, 
															intno_nota AS nm, 
															SUM(totalharga) AS om 
														FROM 
															(SELECT 
																intid_dealer, 
																intno_nota, 
																SUM(intquantity * intharga) AS totalharga, 
																intid_jbarang 
															FROM 
																nota a 
																INNER JOIN nota_detail ON a.intid_nota = nota_detail.intid_nota 
																INNER JOIN barang ON barang.intid_barang = nota_detail.intid_barang 
															WHERE 
																a.intid_week = $week 
																AND a.intid_cabang = $cabang 
																AND a.intid_jpenjualan = 1 
																AND a.is_arisan = 0 
																AND a.is_dp = 0 
																AND a.inttotal_bayar > 0
																AND nota_detail.is_free = 0 
															GROUP BY intno_nota, intid_jbarang
															) AS z 
														WHERE intid_jbarang = 2 
														GROUP BY intno_nota
														) AS omsetmetal
													ON omsettulip.nt = omsetmetal.nm
													LEFT JOIN
														(SELECT 
															intid_dealer AS dtc, 
															intno_nota AS ntc, 
															SUM(totalharga) AS otc 
														FROM 
															(SELECT 
																intid_dealer, 
																intno_nota, 
																SUM(intquantity * intharga) AS totalharga, 
																intid_jbarang 
															FROM 
																nota a 
																INNER JOIN nota_detail ON a.intid_nota = nota_detail.intid_nota 
																INNER JOIN barang ON barang.intid_barang = nota_detail.intid_barang 
															WHERE 
																a.intid_week = $week 
																AND a.intid_cabang = $cabang 
																AND a.intid_jpenjualan = 1 
																AND a.is_arisan = 0 
																AND a.is_dp = 0
																AND a.inttotal_bayar > 0 
																AND nota_detail.is_free = 0 
															GROUP BY intno_nota, intid_jbarang
															) AS z 
														WHERE 
															intid_jbarang = 3 
														GROUP BY intno_nota
														) AS omsettc
														ON omsettulip.nt = omsettc.ntc OR omsetmetal.nm = omsettc.ntc
																	UNION
														SELECT * FROM 
																	(SELECT intid_dealer, intno_nota, SUM(totalharga) AS omsettulip FROM (SELECT intid_dealer, intno_nota, SUM(intquantity * intharga) AS totalharga, intid_jbarang FROM nota a INNER JOIN nota_detail ON a.intid_nota = nota_detail.intid_nota INNER JOIN barang ON barang.intid_barang = nota_detail.intid_barang WHERE a.intid_week = $week AND a.intid_cabang = $cabang AND a.intid_jpenjualan = 1 AND a.is_arisan = 0 AND a.is_dp = 0 AND nota_detail.is_free = 0 GROUP BY intno_nota, intid_jbarang) AS z WHERE intid_jbarang = 1 GROUP BY intno_nota) AS omsettulip 
																		RIGHT JOIN 
																	(SELECT intid_dealer, intno_nota, SUM(totalharga) AS omsettulip FROM (SELECT intid_dealer, intno_nota, SUM(intquantity * intharga) AS totalharga, intid_jbarang FROM nota a INNER JOIN nota_detail ON a.intid_nota = nota_detail.intid_nota INNER JOIN barang ON barang.intid_barang = nota_detail.intid_barang WHERE a.intid_week = $week AND a.intid_cabang = $cabang AND a.intid_jpenjualan = 1 AND a.is_arisan = 0 AND a.is_dp = 0 AND nota_detail.is_free = 0 GROUP BY intno_nota, intid_jbarang) AS z WHERE intid_jbarang = 2 GROUP BY intno_nota) AS omsetmetal 
																	ON omsettulip.intno_nota = omsetmetal.intno_nota
																	LEFT JOIN
													(SELECT intid_dealer, intno_nota, SUM(totalharga) AS omsettulip FROM (SELECT intid_dealer, intno_nota, SUM(intquantity * intharga) AS totalharga, intid_jbarang FROM nota a INNER JOIN nota_detail ON a.intid_nota = nota_detail.intid_nota INNER JOIN barang ON barang.intid_barang = nota_detail.intid_barang WHERE a.intid_week = $week AND a.intid_cabang = $cabang AND a.intid_jpenjualan = 1 AND a.is_arisan = 0 AND a.is_dp = 0 AND nota_detail.is_free = 0 GROUP BY intno_nota, intid_jbarang) AS z WHERE intid_jbarang = 3 GROUP BY intno_nota) AS omsettc
																	ON omsettulip.intno_nota = omsettc.intno_nota OR omsetmetal.intno_nota = omsettc.intno_nota
																	UNION 
																	SELECT * FROM 
																	(SELECT intid_dealer, intno_nota, SUM(totalharga) AS omsettulip FROM (SELECT intid_dealer, intno_nota, SUM(intquantity * intharga) AS totalharga, intid_jbarang FROM nota a INNER JOIN nota_detail ON a.intid_nota = nota_detail.intid_nota INNER JOIN barang ON barang.intid_barang = nota_detail.intid_barang WHERE a.intid_week = $week AND a.intid_cabang = $cabang AND a.intid_jpenjualan = 1 AND a.is_arisan = 0 AND a.is_dp = 0 AND nota_detail.is_free = 0 GROUP BY intno_nota, intid_jbarang) AS z WHERE intid_jbarang = 1 GROUP BY intno_nota) AS omsettulip 
																	RIGHT JOIN 
																	(SELECT intid_dealer, intno_nota, SUM(totalharga) AS omsettulip FROM (SELECT intid_dealer, intno_nota, SUM(intquantity * intharga) AS totalharga, intid_jbarang FROM nota a INNER JOIN nota_detail ON a.intid_nota = nota_detail.intid_nota INNER JOIN barang ON barang.intid_barang = nota_detail.intid_barang WHERE a.intid_week = $week AND a.intid_cabang = $cabang AND a.intid_jpenjualan = 1 AND a.is_arisan = 0 AND a.is_dp = 0 AND nota_detail.is_free = 0 GROUP BY intno_nota, intid_jbarang) AS z WHERE intid_jbarang = 2 GROUP BY intno_nota) AS omsetmetal
																	ON omsettulip.intno_nota = omsetmetal.intno_nota
																	RIGHT JOIN
																	(SELECT intid_dealer, intno_nota, SUM(totalharga) AS omsettulip FROM (SELECT intid_dealer, intno_nota, SUM(intquantity * intharga) AS totalharga, intid_jbarang FROM nota a INNER JOIN nota_detail ON a.intid_nota = nota_detail.intid_nota INNER JOIN barang ON barang.intid_barang = nota_detail.intid_barang WHERE a.intid_week = $week AND a.intid_cabang = $cabang AND a.intid_jpenjualan = 1 AND a.is_arisan = 0 AND a.is_dp = 0 AND nota_detail.is_free = 0 GROUP BY intno_nota, intid_jbarang) AS z WHERE intid_jbarang = 3 GROUP BY intno_nota) AS omsettc
																	ON omsettulip.intno_nota = omsettc.intno_nota OR omsetmetal.intno_nota = omsettc.intno_nota) AS omset
															) AS omset
										LEFT JOIN 
										(SELECT intid_dealer AS dv, intno_nota AS nv, intvoucher AS v FROM nota) AS voucher
										ON omset.n = voucher.nv
							ORDER BY intno_nota");
        
		return $query->result();

	}
	function get_CetakSales($week, $cabang)
	{
//001
//009
		$query=$this->db->query("SELECT * FROM 
		(select a.datetgl, 
					a.intno_nota, 
					f.strnama_jpenjualan, 
					c.strnama_dealer, 
					c.strnama_upline, 
					d.strnama_unit, 
					a.inttotal_omset, 
					IF(intid_nota = ANY (SELECT nota.intid_nota FROM `nota` WHERE `intid_jpenjualan` = 5 OR `intid_jpenjualan` = 7), '0', IF(intid_nota = ANY (SELECT nota.intid_nota FROM `nota` WHERE `intid_jpenjualan` = 4), a.inttotal_omset, a.intomset10)) AS intomset10, 
					IF(intid_nota = ANY (SELECT nota.intid_nota FROM `nota` WHERE `intid_jpenjualan` = 7), '0', a.intomset20) AS intomset20, 
					(SELECT SUM(nota_detail.intquantity*nota_detail.intharga) 
						FROM nota INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang
						WHERE nota.intid_week = $week
						AND nota.intid_cabang = $cabang
						AND (nota.intid_jpenjualan = 5 OR nota.intid_jpenjualan = 7)
						AND nota.intid_nota = a.intid_nota
					) AS omsetnetto,
					(SELECT SUM(nota_detail.intquantity*nota_detail.intharga) 
						FROM nota INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang
						WHERE nota.intid_week = $week
						AND nota.intid_cabang = $cabang
						AND nota.intid_jpenjualan = 11
						AND nota.intid_nota = a.intid_nota
					) AS omsetspecialprice,
					(SELECT SUM(nota_detail.intquantity*nota_detail.intharga) 
						FROM nota INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang
						WHERE nota.intid_week = $week
						AND nota.intid_cabang = $cabang
						AND nota.intid_jpenjualan = 12
						AND nota.intid_nota = a.intid_nota
					) AS omsetpoint,
					(SELECT DISTINCT nota.inttotal_bayar 
						FROM nota INNER JOIN nota_detail ON nota.intid_nota = nota_detail.intid_nota INNER JOIN barang ON nota_detail.intid_barang = barang.intid_barang
						WHERE nota.intid_week = $week
						AND nota.intid_cabang = $cabang
						AND nota.intid_jpenjualan = 10
						AND nota.intid_nota = a.intid_nota
					) AS omsetsk,
					(select sum(nota_detail.intquantity*nota_detail.intharga) 
						from nota, nota_detail, barang 
						where nota.intid_nota = nota_detail.intid_nota
						and nota_detail.intid_barang = barang.intid_barang
						and nota.intid_week = $week
						and nota.intid_cabang = $cabang
						and nota.is_arisan = 0
						and nota.is_dp = 0
						and barang.intid_jbarang=5
						and nota.intid_nota = a.intid_nota
					) as omsetlg,
					(select sum(nota_detail.intquantity*nota_detail.intharga) 
						from nota, nota_detail, barang 
						where nota.intid_nota = nota_detail.intid_nota
						and nota_detail.intid_barang = barang.intid_barang
						and nota.intid_week = $week
						and nota.intid_cabang = $cabang
						and barang.intid_jbarang=6
						and nota.is_arisan = 0
						and nota.is_dp = 0
						and nota.intid_nota = a.intid_nota
					) as omsetll,
					a.inttotal_bayar, 
					a.intpv, 
					a.intid_nota, 
					b.strnama_cabang, 
					a.intid_week, 
					(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intid_week = $week) AS datestart,
					(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week = $week) AS dateend, a.is_arisan, 
					(select winner from arisan where intid_arisan_detail= a.intid_nota) winner
		from nota a, cabang b, member c, unit d, jenis_penjualan f
		where c.intid_unit = d.intid_unit
			and a.intid_cabang = b.intid_cabang
			and a.intid_dealer =  c.intid_dealer
			and a.intid_jpenjualan = f.intid_jpenjualan
			and a.intid_cabang = $cabang
			and a.intid_week = $week
			and a.is_dp = 0 
		order by a.intid_jpenjualan asc, a.intno_nota asc) AS z
UNION
SELECT * FROM (
SELECT a.datetgl, a.intno_nota, 'Hadiah' AS strnama_jpenjualan, c.strnama_dealer, c.strnama_upline, d.strnama_unit, 
' ' AS inttotal_omset, ' ' AS intomset10, ' ' AS intomset20, ' ' AS omsetnetto, ' ' AS omsetspecialprice, 
' ' AS omsetpoint, ' ' AS omsetsk, ' ' AS omsetlg, ' ' AS omsetll, ' ' AS omsettotal_bayar, ' ' AS intpv, 
a.intid_nota, b.strnama_cabang, a.intid_week, 
(select date_format(dateweek_start, '%d %M %Y') AS dateweek_start  from week where intid_week = $week) AS datestart, 
(select date_format(dateweek_end, '%d %M %Y') AS dateweek_end from week where intid_week = $week) AS dateend, 
' ' AS is_arisan, ' ' AS winner 
FROM nota_hadiah a INNER JOIN cabang b ON a.intid_cabang = b.intid_cabang INNER JOIN member c ON a.intid_dealer = c.intid_dealer INNER JOIN unit d ON c.intid_unit = d.intid_unit 
WHERE a.intid_cabang = $cabang AND a.intid_week = $week) AS x");
        return $query->result();
	}
	function getLap_Stok_cab_3($intid_cabang, $week){
			$query0 = $this->db->query("select date(as_of_date) start from stok_barang where intid_cabang = '".$intid_cabang."' limit 0,1 ");
			$hasil0 = $query0->result();
			$weekbefore = $week-1;
			$query1 = $this->db->query("select dateweek_end from week where intid_week = ".$weekbefore."");
			$hasil1 = $query1->result();
			if($hasil0[0]->start >= $hasil1[0]->dateweek_end){
				$weekbefore = 0 ;
			} else{
				//Do Nothing
			}
			$select = 'select barang.strnama,
									  		barang.intid_barang,
											barang.intid_jsatuan, 
											barang_masuk_before.jum_barang_masuk_before, 
											barang_keluar_before.jum_barang_keluar_before,  
											barang_keluar_hadiah_before.jum_barang_keluar_hadiah_before, 
											barang_masuk_after.jum_barang_masuk_after, 
											barang_keluar_after.jum_barang_keluar_after,
											barang_keluar_hadiah_after.jum_barang_keluar_hadiah_after,
											stok_barang.set_fisik, 
											stok_barang.pcs_fisik,
											stok_barang.set_hutang, 
											stok_barang.pcs_hutang
									  from
									  barang left join
									  (select sum(intquantity) jum_barang_keluar_before, nd.intid_barang 
										  from 
										  nota n inner join nota_detail nd on nd.intid_nota = n.intid_nota 
										  where n.intid_cabang = "'.$intid_cabang.'"
										  and n.intid_week <= "'.$weekbefore.'"
										  and n.datetgl > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang limit 0,1) group by nd.intid_barang
										  )as barang_keluar_before
									  on barang_keluar_before.intid_barang = barang.intid_barang
									  left join 
									  (select sum(intquantity) jum_barang_keluar_hadiah_before, nd.intid_barang 
										  from 
										  nota_hadiah n inner join nota_detail_hadiah nd on nd.intid_nota = n.intid_nota 
										  where n.intid_cabang = "'.$intid_cabang.'"
										  and n.intid_week <= "'.$weekbefore.'"
										  and n.datetgl > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang limit 0,1) group by nd.intid_barang
										)as barang_keluar_hadiah_before 
									  on barang_keluar_hadiah_before.jum_barang_keluar_hadiah_before = barang.intid_barang
									  left join
									  (select sum(spd.quantity) jum_barang_masuk_before, spd.intid_barang 
									   		from 
											spkb inner join spkb_detail spd on spd.no_spkb = spkb.no_spkb
											where spkb.intid_cabang = "'.$intid_cabang.'"
											and spkb.week <= "'.$weekbefore.'"
											and spkb.tgl_terima > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang where intid_cabang = "'.$intid_cabang.'" limit 0,1) 
											and spkb.no_sj != ""
											and terkirim = 1
											group by spd.intid_barang	
									   	  )as barang_masuk_before
									   on barang_masuk_before.intid_barang = barang.intid_barang
									   left join
									  (select sum(intquantity) jum_barang_keluar_after, nd.intid_barang 
										  from 
										  nota n inner join nota_detail nd on nd.intid_nota = n.intid_nota 
										  where n.intid_cabang = "'.$intid_cabang.'"
										  and n.intid_week = "'.$week.'"
										  and n.is_dp = 0
										  and n.datetgl > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang where intid_cabang = "'.$intid_cabang.'" limit 0,1) group by nd.intid_barang
										  )as barang_keluar_after
									  on barang_keluar_after.intid_barang = barang.intid_barang
									  left join
									  (select sum(ndh.intquantity) jum_barang_keluar_hadiah_after, ndh.intid_barang 
										   from 
										   nota_hadiah nh inner join nota_detail_hadiah ndh on ndh.intid_nota = nh.intid_nota 
										   where nh.intid_cabang = "'.$intid_cabang.'"
										   and nh.intid_week = "'.$week.'"
										   and nh.datetgl > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang where intid_cabang = "'.$intid_cabang.'" limit 0,1) group by ndh.intid_barang
										   )as barang_keluar_hadiah_after
									  on barang_keluar_hadiah_after.intid_barang = barang.intid_barang
									  left join
									  (select sum(spd.quantity) jum_barang_masuk_after, spd.intid_barang 
									   		from 
											spkb inner join spkb_detail spd on spd.no_spkb = spkb.no_spkb
											where spkb.intid_cabang = "'.$intid_cabang.'"
											and spkb.week = "'.$week.'"
											and spkb.tgl_terima > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang where intid_cabang = "'.$intid_cabang.'" limit 0,1) 
											and spkb.no_sj != ""
											and terkirim = 1
											group by spd.intid_barang	
									   	  )as barang_masuk_after
									   on barang_masuk_after.intid_barang = barang.intid_barang
									   left join 
									   (select *
											from 
											stok_barang
											where intid_cabang = "'.$intid_cabang.'"
											)as stok_barang
									   on stok_barang.intid_barang = barang.intid_barang
									  order by barang.strnama asc
									  ';
								//	  echo "".$select;
			$query = $this->db->query($select);
			return $query->result();
		}
		
		function get_jenis_notaHadiah($data){
		$SEL	=	$this->db->query('select upper(jenis_nota) jenis_nota, kode from jenis_nota_hadiah where status = 1 order by jenis_nota asc');
		$var = "<select name='".$data['name']."' id = ".$data['id']." class='".$data['id']."'>";
		$data = "";
		foreach($SEL->result() as $rok){
			if($data == ""){
				$data ="<option value='".$rok->kode."'selected>".$rok->jenis_nota."</option>";
			}else{
				$data .= "<option value='".$rok->kode."'>".$rok->jenis_nota."</option>";
			}
		}
		$var .= $data."</select>";
		return $var;
		}
		function getLap_Stok_cab_4($intid_cabang, $week){
			//disini lakukan tracking hutang barang
			$select = "select * from stok_barang where intid_cabang = ".$intid_cabang."";
			$query = $this->db->query($select);
			foreach($query->result() as $row){
				//simpan di variabel global
				$this->hutang_barang_set[$row->intid_barang]['hutang_barang'] = $row->set_hutang;
				$this->hutang_barang_pcs[$row->intid_barang]['hutang_barang'] = $row->pcs_hutang;
				//
				$this->sisa_barang_pcs[$row->intid_barang]['set_fisik'] = $row->set_hutang;
				$this->sisa_barang_set[$row->intid_barang]['pcs_fisik'] = $row->pcs_hutang;
			}
			//
			$select2 = "select * from week where intid_week < ".$week." and dateweek_start > (select date_format(as_of_date,'%Y-%m-%d') from stok_barang where intid_cabang = ".$intid_cabang." limit 0,1) group by intid_week order by intid_week asc";
			$query2 = $this->db->query($select2);
			foreach($query2->result() as $row){
				//melakukan perhitungan mundur week
				$select3 = "(select 
					from nota inner join nota_detail 
					on nota_detail.intid_nota = nota.intid_nota
					where nota.intid_week = '".$row->intid_week."'
					and nota.intid_cabang = ".$intid_cabang.")";
			}
			//
			
		}
	}
?>